#!/usr/bin/env node

const mysql = require('mysql2/promise'),
      log   = console.log,
      Koa   = require('koa'),
      app   = new Koa(),
      Router = require('koa-router'),
      router = new Router(),
      fs    = require('fs'),
      path  = require('path'),
      cors  = require('koa2-cors'),
      http  = require('http'),
      https = require('https');

let con = mysql.createPool({
    host:'154.8.156.240',
    port:'2020',
    user:'root',
    password:'Ljj0222.',
    database:'tuijian'
});

//通过openid查询信息
async function get_openid(open_id){
  const sql = `select * from Merchants_settled_table where open_id = '${open_id}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//查询图片
async function get_img(open_id,zl){
  const sql = `select img from shop_img_table where open_id = '${open_id}' and zl ='${zl}'`; 
  let [rows] = await con.execute(sql);
  return rows;
}
async function get_img1(img){
  const sql = `select * from shop_img_table where img = '${img}'`; 
  let [rows] = await con.execute(sql);
  return rows;
}
//删除图片
async function del_img(open_id,zl,img){
  const sql = `delete from shop_img_table where open_id = '${open_id}' and zl = '${zl}' and img = '${img}'`; 
  log(sql)
  let [rows] = await con.execute(sql);
  return rows;
}
//添加图片
async function insert_img(open_id,zl,img){
  const sql = `insert into shop_img_table(open_id,zl,img) values('${open_id}','${zl}','${img}')`; 
  let [rows] = await con.execute(sql);
  return rows;
}
//更改信息
async function update_change(open_id, field, content){
  const sql = `update Merchants_settled_table set ${field} = '${content}' where open_id = '${open_id}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//通过id查询
async function get_id(id){
  const sql = `select * from Merchants_settled_table where id = ${id}`;
  let [rows] = await con.execute(sql);
  return rows;
}
//删除商家
async function del_shop(open_id){
  const sql = `delete from Merchants_settled_table where open_id = '${open_id}'`;
  log(sql)
  let [rows] = await con.execute(sql);
  return rows;
}
//name模糊搜索
async function get_name(name){
  const sql = `select * from Merchants_settled_table where shop_name like '%${name}%' or shop_addr like '%${name}%'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//hot搜索
async function get_main(id){
  //const sql = `select * from main_table where shop_id = '${id}'`;
  const sql = `select share_id,user_name,title,text,img1 from main_table left join img_table on main_table.share_id = img_table.id left join user_table on main_table.user_id = user_table.id where shop_id = '${id}'`;
  let [rows] =await con.execute(sql);
  return rows;
}
(async function(){
    //log(await getAreas());
})();

module.exports = {
  get_id,
  update_change,
  get_openid,
  get_img,
  insert_img,
  del_img,
  get_img1,
  get_name,
  get_main,
  del_shop
}

